package com.dtn.wsn.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Vector;

/***
 * 
 * ConnectionPool to manage the MYSQL connection
 * 
 * @author hao
 */

public class ConnectionPool {

	private String jdbcDriver = "com.mysql.jdbc.Driver"; // database driver

	private String dbUrl = "jdbc:mysql://localhost:3306/Test"; // url

	private String dbUsername = "root"; // username for DB

	private String dbPassword = "bytewalla5"; // password

	private String testTable = ""; // default test table

	private int initialConnections = 3; // initial connections of the pool

	private int incrementalConnections = 2;// auto increment size

	private int maxConnections = 9; // Max connections

	private Vector connections = null; // vector to store the connections

	public ConnectionPool() {
	}

	/**
	 * constructor method
	 * 
	 * @param jdbcDriver
	 * @param dbUrl
	 * @param dbUsername
	 * @param dbPassword
	 */

	public ConnectionPool(String jdbcDriver, String dbUrl, String dbUsername,
			String dbPassword) {

		this.jdbcDriver = jdbcDriver;

		this.dbUrl = dbUrl;

		this.dbUsername = dbUsername;

		this.dbPassword = dbPassword;

	}

	/**
	 * create DB connection pool
	 * 
	 */

	public synchronized void createPool() throws Exception {

		if (connections != null) {

			return;

		}

		Driver driver = (Driver) (Class.forName(this.jdbcDriver).newInstance());
		DriverManager.registerDriver(driver);
		connections = new Vector();
		createConnections(this.initialConnections);
		System.out.println("create database pool succuessfully");

	}

	/**
	 * create specific numbers of connections
	 * 
	 * @param numConnections
	 *            number of connections
	 */

	@SuppressWarnings("unchecked")
	private void createConnections(int numConnections) throws SQLException {

		for (int x = 0; x < numConnections; x++) {
			if (this.maxConnections > 0
					&& this.connections.size() >= this.maxConnections) {

				break;

			}

			// add a new PooledConnection object to connections vector
			try {
				connections.addElement(new PooledConnection(newConnection()));

			} catch (SQLException e) {

				System.out.println("crate fail !" + e.getMessage());

				throw new SQLException();

			}

			System.out.println("connection create!");

		}

	}

	/**
	 * 
	 * create one connection to database
	 * 
	 * @return a connection
	 * 
	 */

	private Connection newConnection() throws SQLException {

		Connection conn = DriverManager.getConnection(dbUrl, dbUsername,
				dbPassword);
		if (connections.size() == 0) {
			DatabaseMetaData metaData = conn.getMetaData();
			int driverMaxConnections = metaData.getMaxConnections();
			if (driverMaxConnections > 0
					&& this.maxConnections > driverMaxConnections) {
				this.maxConnections = driverMaxConnections;

			}

		}

		return conn;

	}

	/**
	 * 
	 * return a free connection in a pool
	 * 
	 * if no free connection, try later
	 * 
	 * @return connection
	 * 
	 */

	public synchronized Connection getConnection() throws SQLException {
		if (connections == null) {
			return null;
		}
		Connection conn = getFreeConnection();

		while (conn == null) {
			wait(250);
			conn = getFreeConnection();
		}
		return conn;
	}

	/**
	 * 
	 * return a free connection
	 * 
	 * @return available connection
	 * 
	 */

	private Connection getFreeConnection() throws SQLException {
		Connection conn = findFreeConnection();

		if (conn == null) {
			createConnections(incrementalConnections);
			conn = findFreeConnection();
			if (conn == null) {
				return null;
			}
		}
		return conn;

	}

	/**
	 * find free connection in a pool
	 * 
	 * @return 
	 * 
	 */

	private Connection findFreeConnection() throws SQLException {
		Connection conn = null;
		PooledConnection pConn = null;
		Enumeration enumerate = connections.elements();
		while (enumerate.hasMoreElements()) {
			pConn = (PooledConnection) enumerate.nextElement();
			if (!pConn.isBusy()) {
				conn = pConn.getConnection();
				pConn.setBusy(true);
				if (!testConnection(conn)) {
					try {
						conn = newConnection();
					} catch (SQLException e) {
						System.out.println("fail create the connection"
								+ e.getMessage());
						return null;
					}
					pConn.setConnection(conn);
				}
				break;
			}
		}
		return conn;

	}

	/**
	 * test connection is available
	 * 
	 * @param conn
	 * 
	 * @return
	 */

	private boolean testConnection(Connection conn) {

		try {
			if (testTable.equals("")) {
				conn.setAutoCommit(true);

			} else {
				// check if this connection is valid
				Statement stmt = conn.createStatement();

				ResultSet rs = stmt.executeQuery("select count(*) from "
						+ testTable);
				rs.next();
			}

		} catch (SQLException e) {
			e.printStackTrace();
			closeConnection(conn);
			return false;

		}
		return true;

	}

	/**
	 * return a connection to a pool
	 * set the flag to free
	 * 
	 */

	public void freeConnection(Connection conn) {
		if (connections == null) {
			System.out.println("fail return connection!");
			return;
		}
		PooledConnection pConn = null;
		Enumeration enumerate = connections.elements();

		while (enumerate.hasMoreElements()) {
			pConn = (PooledConnection) enumerate.nextElement();
			if (conn == pConn.getConnection()) {
				pConn.setBusy(false);
				break;
			}

		}

	}

	/**
	 * refresh all the connection in database
	 */

	public synchronized void refreshConnections() throws SQLException {
		if (connections == null) {
			return;
		}

		PooledConnection pConn = null;
		Enumeration enumerate = connections.elements();
		while (enumerate.hasMoreElements()) {
			pConn = (PooledConnection) enumerate.nextElement();
			if (pConn.isBusy()) {
				wait(5000);
			}
			closeConnection(pConn.getConnection());
			pConn.setConnection(newConnection());
			pConn.setBusy(false);
		}

	}

	/**
	 *close all the connection in a pool 
	 */

	public synchronized void closeConnectionPool() throws SQLException {
		if (connections == null) {
			return;
		}
		PooledConnection pConn = null;
		Enumeration enumerate = connections.elements();
		while (enumerate.hasMoreElements()) {
			pConn = (PooledConnection) enumerate.nextElement();
			if (pConn.isBusy()) {
				wait(5000); 
			}
			closeConnection(pConn.getConnection());
			connections.removeElement(pConn);
		}
		connections = null;
	}

	/**
	 * close connection
	 */
	private void closeConnection(Connection conn) {

		try {

			conn.close();

		} catch (SQLException e) {

			System.out.println("close failed" + e.getMessage());

		}

	}

	/**
	 *process wait mSeconds
	 */
	private void wait(int mSeconds) {

		try {

			Thread.sleep(mSeconds);

		} catch (InterruptedException e) {

		}

	}

	public int getInitialConnections() {

		return this.initialConnections;

	}

	public void setInitialConnections(int initialConnections) {

		this.initialConnections = initialConnections;

	}

	public int getIncrementalConnections() {

		return this.incrementalConnections;

	}

	public void setIncrementalConnections(int incrementalConnections) {

		this.incrementalConnections = incrementalConnections;

	}

	public int getMaxConnections() {
		return this.maxConnections;

	}

	public void setMaxConnections(int maxConnections) {

		this.maxConnections = maxConnections;

	}

	public String getTestTable() {
		return this.testTable;

	}

	public void setTestTable(String testTable) {
		this.testTable = testTable;

	}

	/**
	 * Inner class 
	 * connections in the pool with flag indicates wheather connection is used
	 */

	class PooledConnection {

		Connection connection = null;
		boolean busy = false;

		public PooledConnection(Connection connection) {
			this.connection = connection;
		}

		public Connection getConnection() {
			return connection;
		}

		public void setConnection(Connection connection) {
			this.connection = connection;
		}

		public boolean isBusy() {
			return busy;
		}

		public void setBusy(boolean busy) {

			this.busy = busy;

		}

	}

}
